package cn.xenosp.json2xls;

import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.*;

/**
 * 项目名： zhengzhou_keyan
 * 包路径： com.waysoft.json2xls
 * 作者：   TongHui
 * 创建时间: 2024-07-03 15:57
 * 描述: json 转 xls 处理器
 * 版本: 1.0
 */
public class Json2xlsHandle {

    private byte[] filebyte = null; //文件数据

    private Workbook workbook;
    private ExcelType excelType;

    private Json2xlsHandle(){

    }

    private Json2xlsHandle(ExcelType excelType){
        this.excelType = excelType;
        if (excelType.code == 1){
            this.workbook = new HSSFWorkbook();
        }
        if (excelType.code == 2){
            this.workbook = new XSSFWorkbook();
        }
    }

    public static void convertToExcel(String jsonStr,ExcelType excelType,String xlsFilePath){
        if (jsonStr == null || jsonStr.length() == 0){
            throw new IllegalStateException("jsonStr IS NULL");
        }
        if (xlsFilePath == null || xlsFilePath.length() == 0){
            throw new IllegalStateException("xlsFilePath IS NULL");
        }
        /**
         * 校验是否是json格式
         */
        if (!JSON.isValid(jsonStr)) {
            throw new IllegalStateException("Invalid JSON");
        }
        Json2xlsHandle json2xlsHandle = new Json2xlsHandle(excelType);
        json2xlsHandle.json2xls(jsonStr);
        json2xlsHandle.saveFile(xlsFilePath);
    }

    /**
     * 入口方法
     * @param json json格式字符串
     */
    private void json2xls(String json){
        List<CellBean> list = new ArrayList<>();
        System.out.println("************************解析json数据******************************");
        if (JSON.isValidArray(json)){
            JSONArray jsonArray = JSON.parseArray(json);
            processJsonArray(jsonArray,0,0,0,list);
        }else {
            JSONObject jsonObject = JSON.parseObject(json);
            processJsonObject(jsonObject,0,0,list,0);
        }
        System.out.println("************************解析json数据******************************");
        // 获取文件
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            System.out.println("************************生成文件******************************");
            createWorkbook(workbook,list);
            workbook.write(outputStream);
            filebyte = outputStream.toByteArray();
            System.out.println("************************生成文件******************************");
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 处理json对象转为结果对象的过程
     * 把每个最小 k:v 元素转为单元格对象
     * @param jsonObject    json数据
     * @param x             行坐标
     * @param y             列坐标
     * @param list          结果数据
     * @param isArrayObj    判断是否是数组下的一个对象
     * @return {
     *     "":{}
     *     "":[]
     *     "":""
     * }  => 返回行数为 本身{}的行 + 子{}的行 + 子[]的行
     * 一个 k:v 为一行
     */
    private int processJsonObject(JSONObject jsonObject,int x,int y, List<CellBean> list,int isArrayObj){
//        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>{进入josn对象解析}");
        int row = x , cell = y;
        Set<String> set = jsonObject.keySet();
        List<String> keyList = new ArrayList<>(set);
        int maxRow = set.size(),objSize = 0,arrLength = 0;
        for (String key : keyList) {
            String value_json = jsonObject.getString(key);
            if (isArrayObj == 0){
                addKeyCell(list,row,cell,key);
            }
            if (JSON.isValidArray(value_json)){
                int row_n = processJsonArray(JSON.parseArray(value_json),row,cell+1,isArrayObj,list);
                /**
                 *  json数组中对象的key最大个数
                 */
                if (arrLength < row_n){
                    arrLength = row_n;
                }
                row += (arrLength<=0?1:arrLength);
            }else if (JSON.isValidObject(value_json)){
                JSONObject parseObject = JSON.parseObject(value_json);
                int row_n = processJsonObject(parseObject,row,cell+1,list,isArrayObj);
                if (objSize < row_n){
                    objSize = row_n;
                }
                row += row_n;
            }else {
                CellBean cellBean = new CellBean();
                cellBean.row = row++;
                cellBean.cell = cell+1;
                cellBean.conent = value_json;
                list.add(cellBean);
            }
        }
//        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>{退出josn对象解析}");
        return maxRow + objSize+ arrLength;
    }



    /**
     * 处理json数组对象转为结果对象的过程
     * 数组中的每个最小元素，转为单元格对象
     * @param jsonArray  json数组数据
     * @param x          行坐标
     * @param y          列坐标
     * @param isArrayObj 是否为数组下的对象
     * @param list       结果数据
     * @return [
     *      []
     *      {}
     *      ""
     * ] => 返回行数为 子[] 的行 + 子{} 的行
     */
    private int processJsonArray(JSONArray jsonArray,int x, int y,int isArrayObj,List<CellBean> list){
//        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>{进入josn数组解析}");
        int row = x , cell = y;
        int maxRow = 0, objSize = 0, arrLength = 0;
        JSONObject parseObject = null;
        for (int i = 0; i < jsonArray.size();i++) {
            String json = jsonArray.getString(i);
            if (JSON.isValidArray(json)){
                int row_n = processJsonArray(JSON.parseArray(json),row,cell,0,list);
                if (arrLength < row_n){
                    arrLength = row_n;
                }
            }else if (JSON.isValidObject(json)){
                if (parseObject == null){
                    parseObject = JSON.parseObject(json);
                }
                /**
                 * 根据第一个 json对象，校验后续对象，缺少补充 “”
                 */
                JSONObject newparseObject = verifyJsonObject(parseObject,JSON.parseObject(json));
                int row_n = processJsonObject(newparseObject,row,(cell+i),list,isArrayObj++);
                if (objSize < row_n){
                    objSize = row_n;
                }
            }else {
                CellBean cellBean = new CellBean();
                cellBean.row = row;
                cellBean.cell = cell+i;
                cellBean.conent = json;
                list.add(cellBean);
            }
        }
//        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>{退出josn数组解析}");
        maxRow = (maxRow + objSize + arrLength)-1;
        return maxRow < 0 ? 0 :maxRow;
    }
    /**
     * 添加key单元格。针对是对象中的key
     */
    private void addKeyCell(List<CellBean> list,int x,int y,String content){
        CellBean cellBean = new CellBean();
        cellBean.row = x;
        cellBean.cell = y;
        cellBean.conent = content;
        cellBean.isKeyCell = true;
        list.add(cellBean);
    }

    /**
     * 校验jion对象数据是否一致
     * 通过 json1 校验 json2 中是否有缺少的kye。
     * 缺少时补充key并设置 ”“
     * 返回新对象
     * @param json1
     * @param json2
     * @return
     */
    private JSONObject verifyJsonObject(JSONObject json1, JSONObject json2) {
        JSONObject newJson = new JSONObject();
        Set<String> set = json1.keySet();
        for (String key : set) {
            if (json2.containsKey(key)) {
                newJson.put(key,json2.getString(key));
                json2.remove(key);
            }else {
                newJson.put(key,"");
            }
        }
        newJson.putAll(json2);
        return newJson;
    }

    /**
     * 生成工作簿并填充数据
     * @param hssfWorkbook
     * @param list
     */
    private void createWorkbook(Workbook hssfWorkbook,List<CellBean> list){
        Sheet sheet = hssfWorkbook.createSheet();
        int maxCell = 0;

        /**
         * 填写数据
         */
        for (CellBean bean : list) {
            Row row = sheet.getRow(bean.row);
            if (row == null) {
                row = sheet.createRow(bean.row);
                row.setHeight((short) 400);
            }

            Cell cell = row.getCell(bean.cell);
            if (cell == null){
                cell = row.createCell(bean.cell);
            }else {
                short lastCellNum = row.getLastCellNum();
                cell =  row.createCell(lastCellNum);
            }

            // 保存最大列
            if (maxCell < cell.getColumnIndex()){
                maxCell = cell.getColumnIndex();
            }

            cell.setCellValue(bean.conent);
            CellStyle cellStyle = hssfWorkbook.createCellStyle();
            if (bean.isKeyCell){
                Font font = hssfWorkbook.createFont();
                font.setBold(true);
                cellStyle.setFont(font);
            }
            cell.setCellStyle(cellStyle);
        }

        /**
         * 渲染样式
         */
        int lastRowNum = sheet.getLastRowNum() ;
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            for (int j = 0; j <= maxCell; j++) {
                Cell cell = row.getCell(j);
                CellStyle cellStyle = null;
                if (cell == null){
                    cell = row.createCell(j);
                    cellStyle = hssfWorkbook.createCellStyle();
                }else {
                    cellStyle = cell.getCellStyle();
                }

                // 自适应列宽
                sheet.autoSizeColumn(j);

                // 设置单元格边框
                cellStyle.setBorderBottom(BorderStyle.THIN);
                cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
                cellStyle.setBorderLeft(BorderStyle.THIN);
                cellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
                cellStyle.setBorderRight(BorderStyle.THIN);
                cellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
                cellStyle.setBorderTop(BorderStyle.THIN);
                cellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());

                cell.setCellStyle(cellStyle);
            }
        }
    }


    /**
     * 传入文件保存路径进行保存
     * @param path
     */
    private void saveFile(String path){
        try {
            String filePath = "";
            if (path.endsWith(".xls") && excelType.code != 1){
                filePath = path.replace(".xls",".xlsx");
            } else
            if (path.endsWith(".xlsx") && excelType.code != 2){
                filePath = path.replace(".xlsx",".xls");
            }else if (!path.endsWith(".xls") && !path.endsWith(".xlsx")){
                filePath = path + File.separator + new Date().getTime()+ (excelType.code == 1?".xls":".xlsx");
            }else {
                filePath = path;
            }
            System.out.println("保存文件：>>>>>>>>"+filePath);
            FileUtils.writeByteArrayToFile(new File(filePath),filebyte);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 单元格javaBean
     */
    class CellBean{
        int row;
        int cell;
        String conent;
        boolean isKeyCell;
    }

   public static enum ExcelType{
        xls(1),xlsx(2);
        int code;
        ExcelType(int code){
            this.code = code;
        }
    }
}
